<?php
/**
 * Semester Subject Controller
 * Semester Subject  Information   Management
 *
 * semester entry
 *
 * @version $Id: SubjectController.php 123 2010-05-30 11:41:51Z yingkuang.chen $
 * @license New BSD License
 * @uses Zend_Controller_Action
 * @package semester
 * @subpackage Controller
 */
class semester_SubjectController extends Zend_Controller_Action
{
    function indexAction()
    {
        $this->_redirect('semester/subject/set');
    }

    // 取得年級陣列
    function getgradeAction()
    {
        $this->_helper->json($this->view->ClassGrade());
    }
    /**
     * 設定畫面
     *
     */
    function setAction()
    {
        //學年學期
        if (!$this->_request->getParam('year_semester'))
        $this->view->year_semester = $this->view->Semester['year'].'-'.$this->view->Semester['semester'];
        else
        $this->view->year_semester = $this->_request->getParam('year_semester');
        //年級
        if (!$this->view->sel_grade = $this->_request->getParam('sel_grade'))
        $this->view->sel_grade = $this->view->lowYear;  // 預設最低學年

        //		$logger = Zend_Registry::get('logger');
        //	$logger->log('This is a log message!', Zend_Log::INFO);

    }

    /**
     * 修改課程
     *
     */
    function editAction()
    {
        $sn = (int) $this->_request->getParam('sn',0);
        if ($sn >0) {
            $q = Doctrine_Query::create()
            ->update('Semester_Model_SemesterSubject')
            ->set('scored','?',(int)$this->_request->getParam('scored'))
            ->set('scored_mode','?',(int)$this->_request->getParam('scored_mode'))
            ->set('rate','?',(float)$this->_request->getParam('rate'))
            ->set('sections','?',(float)$this->_request->getParam('sections'))
            ->where('sn=?',$sn);
            $q->execute();
        }
        $this->_helper->json('ok');
    }


    /**
     * 取得年級班名資料
     * 以 Json 格式送出
     */
    function getyearAction()
    {

        $year_semester = $this->_request->getParam('year_semester',0);
        $arr = explode("-",$year_semester);
        $year= (int)$arr[0];
        $semester = (int)$arr[1];
        $grade = (int)$this->_request->getParam('sel_grade',0);
        if ($year) {
            $q  = Doctrine_Query::create()
            ->from('Semester_Model_SemesterClass')
            ->where('year=?',$year)
            ->andWhere('semester=?',$semester)
            ->andWhere('enable=?','y');
            $row = $q->fetchArray();
            $arr = array();
            foreach($row as $val)
            $arr[$val['grade']][] = $val;
            $this->_helper->Json($arr);
        }

    }

    /**
     * 未設定科目
     * @return JSON Array
     */
    function yetSetAction()
    {
        $this->getHelper ( 'layout' )->disableLayout ();
        $tempArr = explode("-", $this->getRequest()->getParam('year_semester'));
        $year = $tempArr[0];
        $semester = $tempArr[1];
        $tempArr = explode("-",$this->getRequest()->getParam('classSn'));
        $grade = $tempArr[0];
        $classSn = 0;
        if (count($tempArr) > 1)
        $classSn =$tempArr[1];

        $q = Doctrine_Query::create()
        ->select('subject_sn')
        ->from('Semester_Model_SemesterSubject')
        ->where('year=?', $year)
        ->andWhere('semester=?', $semester)
        ->andWhere('grade=?', $grade)
        ->andWhere("enable IS NULL OR enable='Y'");
        // 如果是班級課程
        if ($classSn)
        $q->andWhere('class_sn=?',$classSn);
        else {
              $q->groupby('subject_sn');
        }


        $row = $q->fetchArray();

        // 課程設定 semester_course 中不相符的科目
        // 偵測尚未設定科目
        $q = Doctrine_Query::create()
        ->from('Semester_Model_SemesterCourse')
        ->where('year=?', $year)
        ->andWhere('semester=?', $semester)
        ->andWhere('grade=?', $grade)
        ->andWhere("enable IS NULL OR enable='Y'");
        // 如果是班級課程
        if (!$classSn)
        $q->andWhere('special=?','N');

        $q->orderby('scope_rank, rank');
        $row2 = $q->fetchArray();
        $courseArr = array();
        foreach($row2 as $val){
            $courseArr[$val['sn']] = $val;
        }

        $arr = array();
        $arr2 = array();

        foreach ($row as $val) {
            $arr2[$val['subject_sn']] = 1;
        }
        $yetset = array_diff_key($courseArr,$arr2);
        $this->view->data = $yetset;
    }

    /**
     * 儲存科目設定
     * @return unknown_type
     */
    function saveAction()
    {
        $tempArr = explode("-", $this->getRequest()->getParam('year_semester'));
        $year = $tempArr[0];
        $semester = $tempArr[1];
        $tempArr = explode("-",$this->getRequest()->getParam('classSn'));
        $grade = $tempArr[0];
        $classSn =$tempArr[1];
        $sameWholeGrade = (int)$this->getRequest()->getParam('sameWholeGrade');

        $sections = $this->getRequest()->getParam('sections');
        $scored = $this->getRequest()->getParam('scored');
        $scored_mode = $this->getRequest()->getParam('scored_mode');
        $rate = $this->getRequest()->getParam('rate');
        foreach($sections as $sn=>$val) {
            $q = Doctrine_Query::create()
            ->update('Semester_Model_SemesterSubject')
            ->set('sections','?', $val)
            ->set('rate','?', $rate[$sn]);
            if (isset($scored[$sn]))
            $q->set('scored','?', 'y');
            else
            $q->set('scored','?', 'n');
            if (isset($scored_mode[$sn]))
            $q->set('scored_mode','?', 1);
            else
            $q->set('scored_mode','?', 0);

            // 僅設班級課程
            if (!$sameWholeGrade)
                $q->where('sn=?',$sn);
            // 年級課程設定
            else {
                // 查詢 subject_sn
                $qs = Doctrine_Query::create()
                ->select('subject_sn')
                ->from('Semester_Model_SemesterSubject')
                ->where('sn=?',$sn);
                $qsRes = $qs->fetchOne();
                $subjectSn = $qsRes->subject_sn;

                $q->where('year=?', $year)
                ->andWhere('semester=?',$semester)
                ->andWhere('grade=?', $grade)
                ->andWhere('subject_sn=?',$subjectSn);

                //            echo $year.'-'.$semester.'-'.$grade;
                //            echo $q->getSqlQuery();
            }
            $q->execute();
        }
        $this->_helper->json(1);
    }


    /**
     * 加權同節數設定
     *
     */
    function rate2sectionsAction()
    {
        $tempArr = explode("-", $this->getRequest()->getParam('year_semester'));
        $year = $tempArr[0];
        $semester = $tempArr[1];
        $tempArr = explode("-",$this->getRequest()->getParam('classSn'));
        $grade = $tempArr[0];
        $classSn = 0;
        if (count($tempArr) > 1)
        $classSn =$tempArr[1];

        $q = Doctrine_Query::create()
        ->update('Semester_Model_SemesterSubject')
        ->set('rate','sections')
        ->where('year=?', $year)
        ->andWhere('semester=?', $semester)
        ->andWhere('grade=?', $grade);
        if ($classSn)
        $q->andWHere('class_sn=?', $classSn);

        $q->andWhere('enable=?','y');
        $q->execute();
        $this->_helper->json('ok');
    }


    /**
     * 新增一個科目
     *
     */
    function setonesubjectAction(){
        $tempArr = explode("-", $this->getRequest()->getParam('year_semester'));
        $year = $tempArr[0];
        $semester = $tempArr[1];
        $tempArr = explode("-",$this->getRequest()->getParam('classSn'));
        $grade = $tempArr[0];
        $classSn =$tempArr[1];
         // 是否全年級設定
        $sameChk = $this->getRequest()->getParam('sameChk');
        $subjectSn = (int) $this->getRequest()->getParam('sn');
        // 年級課程
        // 找出這學年有多少班
        $q = Doctrine_Query::create()
        ->from('Semester_Model_SemesterCourse')
        ->select('rank')
        ->where('sn=?',$subjectSn);
        $rank = $q->fetchOne()->rank;


        $q = Doctrine_Query::create()
        ->select('COUNT(*) AS cc')
        ->from('Semester_Model_SemesterClass')
        ->where('year=?',$year)
        ->andWhere('semester=?',$semester)
        ->andWhere('grade=?', $grade);

        $cc = $q->fetchOne()->cc;
        // 非班級課程,自第一班起
        if ($sameChk)
        $classSn = 1;
        else {
            $cc = $classSn;
        }
        for($i=$classSn; $i<=$cc; $i++) {
            // 先查詢是否已新增
            $q = Doctrine_Query::create()
            ->select('sn')
            ->from('Semester_Model_SemesterSubject')
            ->where('year=?',$year)
            ->andwhere('semester=?',$semester)
            ->andwhere('grade=?',$grade)
            ->andwhere('class_sn=?',$i)
            ->andwhere('subject_sn=?',$subjectSn);
            $res = $q->fetchOne();

            if (!$res) {
                $semesterSubject = new Semester_Model_SemesterSubject();
                $semesterSubject->year = $year;
                $semesterSubject->semester = $semester;
                $semesterSubject->grade = $grade;
                $semesterSubject->class_sn = $i;
                $semesterSubject->subject_sn = $subjectSn;
                $semesterSubject->rank = $rank;
                $semesterSubject->save();
            }
        }
        $this->_helper->json('ok');
    }


    /**
     * 取得科目資料
     *
     */
    function getSubjectAction()
    {
        $this->getHelper ( 'layout' )->disableLayout ();

        // 是否為修改模式
        $editMode = ($this->getRequest()->getParam('editMode'))?1:0;
        $this->view->editMode = $editMode;
        $this->view->year_semester = $this->getRequest()->getParam('year_semester');
        $this->view->classSn = $this->getRequest()->getParam('classSn');
        $tempArr = explode("-",$this->getRequest()->getParam('year_semester'));
        $year = (int) $tempArr[0];
        $semester = (int) $tempArr[1];
        $tempArr = explode("-",$this->getRequest()->getParam('classSn'));
        $grade = (int)$tempArr[0];
        if ($tempArr[1])
        $classSn = (int)$tempArr[1];
        else
        $classSn = '';

        $semesterSubject = new Semester_Model_SemesterSubject();
        $this->view->data = $semesterSubject->getSubject($year,$semester,$grade,$classSn);

    }

    /**
     * 刪除一個科目
     *
     */
    function deletesubjectAction()
    {

        $sn = (int)$this->_request->getParam('sn',0);
        //判斷是否有配課資料
        $q = Doctrine_Query::create()
        ->select ('COUNT(*) AS cc')
        ->from ('Semester_Model_SemesterTeacherCourse a')
        ->leftJoin('a.Semester_Model_SemesterSubject b')
        ->where('a.year=b.year AND a.semester=b.semester AND a.grade=b.grade AND a.class_sn=b.class_sn')
        ->andWhere('b.sn=?',$sn);
        //echo $q->getsqlQUery();

        $res = $q->fetchOne();

        if ($res->cc > 0) {
            $this->_helper->json(false);
        }
        else {
            $semesterSubject = new Semester_Model_SemesterSubject();
            $semesterSubject->assignIdentifier($sn);
            $semesterSubject->delete();
            // TODO 刪除科目時判斷是否有成績，如果有成績，則修改科目為　enabled
            $this->_helper->json('deleted ');
        }
    }



}